--EXEC SP_LEFT_TO_BOOK_REPORT '0', '0', '0','0', 'Detail','Forecast','3','2009'

ALTER PROC SP_LEFT_TO_BOOK_REPORT 
@CUSTOMER_NAME VARCHAR(100),
@MRKT_PART_NUMBER VARCHAR(50),
@CHIPSET		 VARCHAR(10),
@CHIP_FAMILY	 VArchar(10),
@REPORT_TYPE	VARCHAR(20),
@FORECAST_TYPE	VARCHAR(20),
@INPUT_QUARTER  INT,
@INPUT_YEAR		INT
AS 
BEGIN
	CREATE TABLE #LEFT_TO_BOOK_TABLE 
	( [CHIP SET] VARCHAR(50),[CHIP FAMILY] VARCHAR(50),[CUSTOMER] VARCHAR(50),
		  [Marketing Part Number] VARCHAR(50), [BASE] VARCHAR(50), [Forecast Current Qtr] INT, [Forecast NEXT Qtr] INT, 
		  [Shipped Current Qtr] INT, [Backlog Curr Qtr] INT, [Backlog Next Qtr] INT, [Curr Left To Book] INT, 
		  [Next Qtr Left To BOOK] INT )
	
	DECLARE @CURR_QUARTER_START_DATE DATETIME
	DECLARE @CURR_QUARTER_END_DATE DATETIME
	DECLARE @NEXT_QUARTER_START_DATE DATETIME
	DECLARE @NEXT_QUARTER_END_DATE DATETIME
	DECLARE @CURRENT_QUARTER INT
	DECLARE @CURRENT_YEAR INT
	DECLARE @NEXT_QUARTER INT
	DECLARE @NEXT_YEAR INT

	DECLARE @IS_PREVIOUS_QTR BIT
	
	DECLARE @RESULT_STRING NVARCHAR(2000)	
	
	SELECT @CURRENT_QUARTER = CQC_QUARTER_NO, @CURR_QUARTER_START_DATE = CQC_START_DATE, @CURR_QUARTER_END_DATE = CQC_END_DATE FROM Company_Quarterly_Calendar WHERE CONVERT( VARCHAR, CURRENT_TIMESTAMP ) >= CQC_START_DATE AND CONVERT( VARCHAR, CURRENT_TIMESTAMP ) <= CQC_END_DATE
	SELECT @NEXT_QUARTER_START_DATE = CQC_START_DATE, @NEXT_QUARTER_END_DATE = CQC_END_DATE FROM Company_Quarterly_Calendar WHERE DATEADD( m, 1, @CURR_QUARTER_END_DATE ) >= CQC_START_DATE AND DATEADD( m, 1, @CURR_QUARTER_END_DATE ) <= CQC_END_DATE

	SET @CURRENT_YEAR = YEAR(@CURR_QUARTER_START_DATE) 
	SET @NEXT_QUARTER = @CURRENT_QUARTER + 1

	IF @INPUT_QUARTER = @CURRENT_QUARTER AND @INPUT_YEAR = @CURRENT_YEAR
	BEGIN
		SET @IS_PREVIOUS_QTR = 0	
	END
	ELSE
	BEGIN
		SET @IS_PREVIOUS_QTR = 1

		SELECT @CURRENT_QUARTER = CQC_QUARTER_NO, @CURR_QUARTER_START_DATE = CQC_START_DATE, @CURR_QUARTER_END_DATE = CQC_END_DATE 
			FROM Company_Quarterly_Calendar CQC, Company_Calendar CC WHERE CQC.CQC_YearID = CC. CC_YearID AND CQC_QUARTER_NO = @INPUT_QUARTER AND CC_Year = @INPUT_YEAR
		SELECT @NEXT_QUARTER_START_DATE = CQC_START_DATE, @NEXT_QUARTER_END_DATE = CQC_END_DATE FROM Company_Quarterly_Calendar WHERE DATEADD( m, 1, @CURR_QUARTER_END_DATE ) >= CQC_START_DATE AND DATEADD( m, 1, @CURR_QUARTER_END_DATE ) <= CQC_END_DATE
		
		SET @CURRENT_YEAR = YEAR(@CURR_QUARTER_START_DATE) 
		SET @NEXT_QUARTER = @CURRENT_QUARTER + 1
	END

	PRINT @IS_PREVIOUS_QTR
	PRINT @CURRENT_YEAR
	PRINT @CURRENT_QUARTER
	PRINT @NEXT_QUARTER
	PRINT @CURR_QUARTER_START_DATE

	IF @NEXT_QUARTER > 4
	BEGIN
		SET @NEXT_QUARTER = @NEXT_QUARTER - 4
		SET @NEXT_YEAR = @CURRENT_YEAR + 1
	END
	ELSE
	BEGIN
		SET @NEXT_YEAR = @CURRENT_YEAR
	END

	--PRINT @CURR_QUARTER_START_DATE
	--PRINT @CURR_QUARTER_END_DATE
	--PRINT @NEXT_QUARTER_START_DATE
	--PRINT @NEXT_QUARTER_END_DATE
	IF @IS_PREVIOUS_QTR > 0
	BEGIN
		INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
		  [Marketing Part Number] , [BASE] , [Backlog Curr Qtr], [Backlog Next Qtr])
		SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
		(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
		Customer_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
		ISNULL((SELECT BK_Requested_Qty FROM Backlog_History_Table BHT1 where BHT1.LineItem_Id = BHT.LineItem_Id AND BHT1.Req_Dock_Date_Qtr_No = @CURRENT_QUARTER and CONVERT( VARCHAR, BHT1.TIMESTAMP, 101) = CONVERT( VARCHAR, @CURR_QUARTER_END_DATE, 101) ), 0),
		ISNULL((SELECT BK_Requested_Qty FROM Backlog_History_Table BHT1 where BHT1.LineItem_Id = BHT.LineItem_Id AND BHT1.Req_Dock_Date_Qtr_No = @NEXT_QUARTER and CONVERT( VARCHAR, BHT1.TIMESTAMP, 101) = CONVERT( VARCHAR, @CURR_QUARTER_END_DATE, 101) ), 0) 
		FROM Backlog_History_Table BHT, Product P
		WHERE BHT.Product_Id = P.Product_ID AND BHT.BK_LineItem_Status <> 'Shipped' AND CONVERT( VARCHAR, BHT.TIMESTAMP, 101) = CONVERT( VARCHAR, @CURR_QUARTER_END_DATE, 101)
	END
	ELSE --Cuurent Qtr
	BEGIN
		
		SET @RESULT_STRING = N'SELECT (SELECT ISNULL( PP.Parameter_Value, '''' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = ''Chipset1''),
		(SELECT ISNULL( PP.Parameter_Value, '''' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = ''Chip Family''),
		C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
		ISNULL((SELECT COOL_QTY FROM Customer_Open_order_lineItem COOL1 where COOL1.LineItem_Id = COOL.LineItem_Id and CONVERT( VARCHAR, COOL1.COOL_requested_Date, 101) <= '+ CONVERT( VARCHAR, @CURR_QUARTER_END_DATE, 101)+' ), 0),
		ISNULL((SELECT COOL_QTY FROM Customer_Open_order_lineItem COOL1 where COOL1.LineItem_Id = COOL.LineItem_Id and CONVERT( VARCHAR, COOL1.COOL_requested_Date, 101) >='+ CONVERT( VARCHAR, @NEXT_QUARTER_START_DATE, 101)+' AND COOL1.COOL_requested_Date <='+ CONVERT( VARCHAR, @NEXT_QUARTER_END_DATE, 101)+' ), 0) FROM
		Customer_Open_Order_Lineitem COOL, Sales_Order SO, Customer C, Product P
		WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_Id = C.Customer_Id AND COOL.Product_Id = P.Product_ID
		AND COOL.COOL_Status <> ''Shipped'' AND SO_IS_CONSIGNMENT = 1'

		PRINT @RESULT_STRING

		INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
			  [Marketing Part Number] , [BASE] , [Backlog Curr Qtr], [Backlog Next Qtr])
		SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
		(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
		C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
		ISNULL((SELECT COOL_QTY FROM Customer_Open_order_lineItem COOL1 where COOL1.LineItem_Id = COOL.LineItem_Id and COOL1.COOL_requested_Date <= CONVERT( VARCHAR, @CURR_QUARTER_END_DATE, 101) ), 0),
		ISNULL((SELECT COOL_QTY FROM Customer_Open_order_lineItem COOL1 where COOL1.LineItem_Id = COOL.LineItem_Id and COOL1.COOL_requested_Date >= CONVERT( VARCHAR, @NEXT_QUARTER_START_DATE, 101) AND COOL1.COOL_requested_Date <= CONVERT( VARCHAR, @NEXT_QUARTER_END_DATE, 101) ), 0) FROM
		Customer_Open_Order_Lineitem COOL, Sales_Order SO, Customer C, Product P
		WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_Id = C.Customer_Id AND COOL.Product_Id = P.Product_ID
		AND COOL.COOL_Status <> 'Shipped' AND SO_IS_CONSIGNMENT = 1
	END

	INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
		  [Marketing Part Number] , [BASE] , [Shipped Current Qtr])
	SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
	(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
	C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
	CSLIT.CSLIT_QTY_SHIPPED FROM Customer_Shipping_Line_item_Table CSLIT, Customer_Open_Order_Lineitem COOL, Sales_Order SO, Customer C, Product P
	WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_Id = C.Customer_Id AND COOL.Product_Id = P.Product_ID
	AND CSLIT.CSLIT_Status = 'SHIPPED' AND SO_IS_CONSIGNMENT = 1 AND CSLIT.CSLIT_SHIPPED_DATE >= @CURR_QUARTER_START_DATE AND CSLIT.CSLIT_SHIPPED_DATE <= @CURR_QUARTER_END_DATE
	
	IF @FORECAST_TYPE = 'BuildForecast'
	BEGIN
		IF @IS_PREVIOUS_QTR > 0
		BEGIN
			INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
				  [Marketing Part Number] , [BASE], [Forecast Current Qtr], [Forecast NEXT Qtr])
			SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
			(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
			C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
			(SELECT QTY FROM FCBUILDHISTORY F1 WHERE F1. ROWID = F.ROWID AND CONVERT( VARCHAR, F1.ENTRYDATE, 101) = (SELECT CONVERT( VARCHAR, MAX(ENTRYDATE), 101) FROM FCBUILDHISTORY WHERE ENTRYDATE <= @CURR_QUARTER_END_DATE ) AND Quarter = @CURRENT_QUARTER AND YEAR = @CURRENT_YEAR ),
			(SELECT QTY FROM FCBUILDHISTORY F1 WHERE F1. ROWID = F.ROWID AND CONVERT( VARCHAR, F1.ENTRYDATE, 101) = (SELECT CONVERT( VARCHAR, MAX(ENTRYDATE), 101) FROM FCBUILDHISTORY WHERE ENTRYDATE <= @CURR_QUARTER_END_DATE ) AND Quarter = @NEXT_QUARTER AND YEAR = @NEXT_YEAR )
			FROM  FCBUILDHISTORY F, Customer C, Product P
			WHERE F.ProdId = P.Product_ID AND F.CustID = C.Customer_ID
		END
		ELSE--FOR CURRENT QUARTER
		BEGIN
			INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
				  [Marketing Part Number] , [BASE], [Forecast Current Qtr], [Forecast NEXT Qtr])
			SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
			(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
			C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
			(SELECT QTY FROM FCBUILDMASTER F1 WHERE F1. ROWID = F.ROWID AND Quarter = @CURRENT_QUARTER AND YEAR = @CURRENT_YEAR ),
			(SELECT QTY FROM FCBUILDMASTER F1 WHERE F1. ROWID = F.ROWID AND Quarter = @NEXT_QUARTER AND YEAR = @NEXT_YEAR )
			FROM  FCBUILDMASTER F, Customer C, Product P
			WHERE F.ProdId = P.Product_ID AND F.CustID = C.Customer_ID
		END
	END
	ELSE--Forecast Report
	BEGIN
		IF @IS_PREVIOUS_QTR > 0
		BEGIN				
			INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
				  [Marketing Part Number] , [BASE], [Forecast Current Qtr], [Forecast NEXT Qtr])
			SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
			(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
			C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
			(SELECT QTY FROM FCENTRYHISTORY F1 WHERE F1. ROWID = F.ROWID AND CONVERT( VARCHAR, F1.ENTRYDATE, 101) = (SELECT CONVERT( VARCHAR, MAX(ENTRYDATE), 101) FROM FCENTRYHISTORY WHERE ENTRYDATE <= @CURR_QUARTER_END_DATE ) AND Quarter = @CURRENT_QUARTER AND YEAR = @CURRENT_YEAR ),
			(SELECT QTY FROM FCENTRYHISTORY F1 WHERE F1. ROWID = F.ROWID AND CONVERT( VARCHAR, F1.ENTRYDATE, 101) = (SELECT CONVERT( VARCHAR, MAX(ENTRYDATE), 101) FROM FCENTRYHISTORY WHERE ENTRYDATE <= @CURR_QUARTER_END_DATE ) AND Quarter = @NEXT_QUARTER AND YEAR = @NEXT_YEAR )
			FROM  FCENTRYHISTORY F, Customer C, Product P
			WHERE F.ProdId = P.Product_ID AND F.CustID = C.Customer_ID AND  CONVERT( VARCHAR, F.ENTRYDATE, 101) = (SELECT CONVERT( VARCHAR, MAX(ENTRYDATE), 101) FROM FCENTRYHISTORY WHERE ENTRYDATE <= @CURR_QUARTER_END_DATE )
		END
		ELSE--FOR CURRENT QUARTER
		BEGIN
			INSERT INTO #LEFT_TO_BOOK_TABLE ( [CHIP SET] ,[CHIP FAMILY] ,[CUSTOMER] ,
			[Marketing Part Number] , [BASE], [Forecast Current Qtr], [Forecast NEXT Qtr])
			SELECT (SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chipset1'),
			(SELECT ISNULL( PP.Parameter_Value, '' ) FROM Process_Parameter PP WHERE PP.ProcessStep_Id = P.ProcessStep_ID AND Parameter_key = 'Chip Family'),
			C.C_Name, P.P_Marketing_Part_number, (SELECT PART_NUMBER FROM PRODUCT WHERE Product_ID = P.P_Parent_Product_ID),
			(SELECT QTY FROM FCENTRYMASTER F1 WHERE F1. ROWID = F.ROWID AND Quarter = @CURRENT_QUARTER AND YEAR = @CURRENT_YEAR ),
			(SELECT QTY FROM FCENTRYMASTER F1 WHERE F1. ROWID = F.ROWID AND Quarter = @NEXT_QUARTER AND YEAR = @NEXT_YEAR )
			FROM  FCENTRYMASTER F, Customer C, Product P
			WHERE F.ProdId = P.Product_ID AND F.CustID = C.Customer_ID
		END
	END


	IF @REPORT_TYPE = 'Detail'
	BEGIN

		SET @RESULT_STRING = N'
		SELECT [CHIP SET],[CHIP FAMILY],[CUSTOMER],[Marketing Part Number], [BASE], SUM([Forecast Current Qtr]) AS [Forecast Current Qtr], SUM([Forecast NEXT Qtr]) AS [Forecast NEXT Qtr], SUM([Shipped Current Qtr]) AS [Shipped Current Qtr], SUM([Backlog Curr Qtr]) AS [Backlog Curr Qtr], SUM([Backlog Next Qtr]) AS [Backlog Next Qtr],
		(ISNULL(SUM([Forecast Current Qtr]), 0)-ISNULL(SUM([Shipped Current Qtr]), 0)-  ISNULL(SUM([Backlog Curr Qtr]), 0) ) as [Curr Left To Book],
		(ISNULL(SUM([Forecast NEXT Qtr]), 0) -  ISNULL(SUM([Backlog Next Qtr]), 0) ) as [Next Qtr Left To BOOK]
		FROM #LEFT_TO_BOOK_TABLE WHERE 1 = 1 '
	END
	ELSE
	BEGIN
		SET @RESULT_STRING = N'
		SELECT [BASE], [CHIP SET],[CHIP FAMILY], SUM([Forecast Current Qtr]) AS [Forecast Current Qtr], SUM([Forecast NEXT Qtr]) AS [Forecast NEXT Qtr], SUM([Shipped Current Qtr]) AS [Shipped Current Qtr], SUM([Backlog Curr Qtr]) AS [Backlog Curr Qtr], SUM([Backlog Next Qtr]) AS [Backlog Next Qtr],
		(ISNULL(SUM([Forecast Current Qtr]), 0)-ISNULL(SUM([Shipped Current Qtr]), 0)-  ISNULL(SUM([Backlog Curr Qtr]), 0) ) as [Curr Left To Book],
		(ISNULL(SUM([Forecast NEXT Qtr]), 0) -  ISNULL(SUM([Backlog Next Qtr]), 0) ) as [Next Qtr Left To BOOK]
		FROM #LEFT_TO_BOOK_TABLE WHERE 1 = 1 '
	END

	
	IF @CUSTOMER_NAME IS NOT NULL AND @CUSTOMER_NAME <> '0'
	BEGIN
			SET @RESULT_STRING = @RESULT_STRING + ' AND [CUSTOMER] = '''+ @CUSTOMER_NAME +''''
	END
	IF @MRKT_PART_NUMBER IS NOT NULL AND @MRKT_PART_NUMBER <> '0'
	BEGIN
			SET @RESULT_STRING = @RESULT_STRING + ' AND [Marketing Part Number] = '''+ @MRKT_PART_NUMBER+''''
	END
	IF @CHIPSET IS NOT NULL AND @CHIPSET <> '0'
	BEGIN
			SET @RESULT_STRING = @RESULT_STRING + ' AND [CHIP SET] = '''+ @CHIPSET+'''' 
	END
	IF @CHIP_FAMILY IS NOT NULL AND @CHIP_FAMILY <> '0'
	BEGIN
			SET @RESULT_STRING = @RESULT_STRING + ' AND [CHIP FAMILY] = ''' + @CHIP_FAMILY+''''
	END
	IF @REPORT_TYPE = 'Detail'
	BEGIN
		SET @RESULT_STRING = @RESULT_STRING + ' GROUP BY [CHIP SET],[CHIP FAMILY],[CUSTOMER],[Marketing Part Number], [BASE] ORDER BY [BASE]'
	END
	ELSE
	BEGIN
		SET @RESULT_STRING = @RESULT_STRING + '  GROUP BY [BASE],[CHIP SET],[CHIP FAMILY] ORDER BY [BASE]'
	END
	--PRINT @RESULT_STRING
	EXEC SP_EXECUTESQL @RESULT_STRING
END